<?
class pago_cxc_perito{

	// Propiedades
	var $id;
	var $id_perito;
	var $codigo_perito;
	var $nombre_perito;
	var $id_zona;
	var $id_zona_padre;
	var $codigo_zona;
	var $nombre_zona;
	var $observaciones;
	var $fecha;
	var $deudas;
	var $pagos;
	var $transferido;
	var $desc_transferido;

	var $total;

	function get($conn, $id){
		try {
			$q = "SELECT A.*, B.codigo AS codigo_perito, B.nombre AS nombre_perito, C.codigo AS codigo_zona, C.nombre AS nombre_zona, COALESCE(C.id_padre,C.id) AS id_zona_padre FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id WHERE A.id = $id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){
				$this->id = $r->fields['id'];
				$this->id_perito = $r->fields['id_perito'];
				$this->codigo_perito = $r->fields['codigo_perito'];
				$this->nombre_perito = $r->fields['nombre_perito'];
				$this->id_zona = $r->fields['id_zona'];
				$this->codigo_zona = $r->fields['codigo_zona'];
				$this->nombre_zona = $r->fields['nombre_zona'];
				$this->id_zona_padre = $r->fields['id_zona_padre'];
				$this->observaciones = $r->fields['observaciones'];
				$this->fecha = $r->fields['fecha'];
				$this->transferido = $r->fields['transferido'];
				
				if($r->fields['transferido'] == "S") {
					$this->desc_transferido = "FACTURADO";
				} else {
					$this->desc_transferido = "PENDIENTE";
				}
				
				$q = "SELECT 1 AS sel, A.*, B.fecha AS fecha_cxc, C.nombre AS nombre_concepto, B.descripcion AS descripcion_cxc, B.saldo + A.monto AS saldo  FROM cobranza_deudas AS A INNER JOIN cxc_peritos AS B ON A.id_cxc = B.id INNER JOIN conceptos AS C ON B.id_concepto = C.id WHERE A.id_cobranza = $id";
				$rCD = $conn->Execute(utf8_encode($q));
				$j=0;

				while(!$rCD->EOF){
					$DeudasAux[$j][0] = $rCF->fields['sel'];
					$DeudasAux[$j][1] = $rCF->fields['id'];
					$DeudasAux[$j][2] = $rCF->fields['fecha_cxc'];
					$DeudasAux[$j][3] = $rCF->fields['nombre_concepto'];
					$DeudasAux[$j][4] = $rCF->fields['descripcion_cxc'];
					$DeudasAux[$j][5] = $rCF->fields['saldo'];
					$DeudasAux[$j][6] = $rCF->fields['monto'];

					$j++;
					$rCD->movenext();
				}
				$this->deudas = new Services_JSON();
				$this->deudas = is_array($DeudasAux) ? $this->deudas->encode($DeudasAux) : false;


				$q = "SELECT A.*,B.nombre AS banco FROM cobranza_formas AS A LEFT JOIN bancos AS B ON A.id_banco = B.id WHERE id_cobranza=$id";
				$rCF = $conn->Execute(utf8_encode($q));
				$i=0;

				while(!$rCF->EOF){
					$PagosAux[$i][0] = $rCF->fields['tipo_pago'];
					$PagosAux[$i][1] = $rCF->fields['id_banco'];
					$PagosAux[$i][2] = $rCF->fields['banco'];
					$PagosAux[$i][3] = $rCF->fields['referencia'];
					$PagosAux[$i][4] = $rCF->fields['monto'];

					$i++;
					$rCF->movenext();
				}
				$this->pagos = new Services_JSON();
				$this->pagos = is_array($PagosAux) ? $this->pagos->encode($PagosAux) : false;

			}


		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function get_all($conn, $orden="id DESC", $Tipo, $Valor, $TipoE, $max=10, $from=1){
		try {
			$zona = $_SESSION['SesionZona'];
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			
			if(empty($Valor)){
				$q = "SELECT A.id FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= " WHERE (C.id = $zona OR C.id_padre = $zona) ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
				$q = "SELECT A.id FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE (C.id = $zona OR C.id_padre = $zona) AND C.nombre::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			//	die($q);			

			}
			elseif($Tipo==1){
				$q = "SELECT A.id FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE (C.id = $zona OR C.id_padre = $zona) AND B.nombre::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==2){
				$q = "SELECT A.id FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE (C.id = $zona OR C.id_padre = $zona) AND  A.fecha::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
				$q = "SELECT A.id FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE (C.id = $zona OR C.id_padre = $zona) AND A.observaciones::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==4){
				$q = "SELECT A.id FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE (C.id = $zona OR C.id_padre = $zona) AND C.codigo::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			//	die($q);			

			}
			elseif($Tipo==5){
				$q = "SELECT A.id FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
				$q.= "WHERE (C.id = $zona OR C.id_padre = $zona) AND B.codigo::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			//	die($q);
			}

			//die($q);			
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new pago_cxc_perito;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}


	function add($conn, $id_perito, $fecha, $observaciones, $deudas, $pagos){
		try {

			$q = "INSERT INTO cobranza ";
			$q.= "(id_perito, fecha, observaciones) ";
			$q.= "VALUES ";
			$q.= "(NULLIF($id_perito,-1), NULLIF('$fecha','')::date, upper('$observaciones')) ";
			//$q.= "RETURNING id; ";

			//die($q);
			$r = $conn->Execute(utf8_encode($q));

			$q2 = "SELECT MAX(id) AS id FROM cobranza";
			$r2 = $conn->Execute(utf8_encode($q2));
			
			$this->GuardarDeudas($conn, $r2->fields['id'],$deudas, $id_perito);
			$this->GuardarPagos($conn, $r2->fields['id'],$pagos);

			//$this->GuardarDeudas($conn, $r->fields['id'],$deudas, $id_perito);
			//$this->GuardarPagos($conn, $r->fields['id'],$pagos);

			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function GuardarPagos($conn,$id,$pagos){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$pagos));
			$q = "DELETE FROM cobranza_formas WHERE id_cobranza=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->pagos)){
				foreach($JsonRec->pagos as $pagosAux){
					$monto = guardafloat($pagosAux[4]);
					$q = "INSERT INTO cobranza_formas ";
					$q.= "(id_cobranza, tipo_pago, id_banco,  referencia, monto) ";
					$q.= "VALUES ";
					$q.= "($id, $pagosAux[0], NULLIF('$pagosAux[1]','')::integer, upper('$pagosAux[3]'), $monto) ";
					
					//die($q);
					$conn->Execute(utf8_encode($q));
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function GuardarDeudas($conn,$id,$deudas,$id_perito){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$deudas));
			$q = "DELETE FROM cobranza_deudas WHERE id_cobranza=$id";
			$r = $conn->Execute(utf8_encode($q));
			if(is_array($JsonRec->deudas)){
				foreach($JsonRec->deudas as $deudasAux){
					$monto = guardafloat($deudasAux[2]);
					$q = "SELECT * FROM distribuye_pago_perito($id, $id_perito, $deudasAux[0], $monto, '$deudasAux[1]');";
					//die($q);
					$conn->Execute(utf8_encode($q));
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function del($conn, $id){
		try {
			$q = "DELETE FROM cobranza WHERE id=$id";
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_DEL_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="id")
	{
		$zona = $_SESSION['SesionZona'];
		$TipoE = empty($TipoE) ? 0 : $TipoE;
		if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE (C.id = $zona OR C.id_padre = $zona)";
		}
		elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id  ";
			$q.= " WHERE (C.id = $zona OR C.id_padre = $zona) AND C.nombre::text ILIKE '%$Valor%'::text";
		}
		elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE (C.id = $zona OR C.id_padre = $zona) AND B.nombre::text ILIKE '%$Valor%'::text";
		}
		elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE (C.id = $zona OR C.id_padre = $zona) AND A.fecha::text ILIKE '%$Valor%'::text";
		}

		elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE (C.id = $zona OR C.id_padre = $zona) AND A.observaciones::text ILIKE '%$Valor%'::text";
		}
		elseif($Tipo==4){
			$q = "SELECT count(*) AS total FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id  ";
			$q.= " WHERE (C.id = $zona OR C.id_padre = $zona) AND C.codigo::text ILIKE '%$Valor%'::text";
		}
		elseif($Tipo==5){
			$q = "SELECT count(*) AS total FROM cobranza AS A INNER JOIN peritos AS B ON A.id_perito = B.id INNER JOIN zonas AS C ON B.id_zona = C.id ";
			$q.= " WHERE (C.id = $zona OR C.id_padre = $zona) AND B.codigo::text ILIKE '%$Valor%'::text";
		}

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}
	
	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}	

	
}
?>
